package com.song.sql;

import org.apache.spark.sql.SparkSession;


/**
 * 开窗函数
 * https://blog.csdn.net/m0_46926492/article/details/124236167
 *
 */
public class TestHiveloadSql001 {
    public static void main(String[] args) {

        // TODO 在编码前，设定Hadoop的访问用户
        System.setProperty("HADOOP_USER_NAME","SongXianYang");

        final SparkSession sparkSession = SparkSession
                .builder()
                .enableHiveSupport() // TODO 启用Hive的支持
                .master("local[*]")
                .appName("SparkSQL")
                .getOrCreate();

        // sparkSession.udf().register("cityRemark", functions.udaf(
        //         new MyCityRemarkUDAF(), Encoders.STRING()
        // ));
        sparkSession.sql("use spark_user_visit_action");


        String sql="select * from  (select *,\n" +
                "       rank() over ( partition by area order by click_count desc ) rk\n" +
                "from (select c.area, p.product_name, count(p.product_id) click_count\n" +
                "      from user_visit_action a\n" +
                "               join city_info c on a.city_id = c.city_id\n" +
                "               join product_info p on p.product_id = a.click_product_id\n" +
                "      where click_category_id != -1\n" +
                "      group by c.area, p.product_name\n" +
                "    limit 1000\n" +
                "    ))t1 where rk <= 3";


        sparkSession.sql(sql).show();
        // TODO 释放资源
        sparkSession.close();

    }
}
